﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using Database;

namespace Database
{
    public class KhachHang
    {
          private string tenkh;

        public string Tenkh
        {
            get { return tenkh; }
            set { tenkh = value; }
        }
        private string matkhau;

        public string Matkhau
        {
            get { return matkhau; }
            set { matkhau = value; }
        }
        private string diachi;

        public string Diachi
        {
            get { return diachi; }
            set { diachi = value; }
        }
        private string dienthoai;

        public string Dienthoai
        {
            get { return dienthoai; }
            set { dienthoai = value; }
        }
        private string email;

        public string Email
        {
            get { return email; }
            set { email = value; }
        }
        private string _quyen;
        public string Quyen { 
            get { return _quyen; }
            set { _quyen = value; }
        }
        public KhachHang()
       {
           
       }
        public KhachHang(string tenkh, string matkhau, string diachi, string dienthoai, string email,string quyen)
        {           
           
            this.tenkh = tenkh;
            this.matkhau = matkhau;
            this.diachi = diachi;
            this.dienthoai = dienthoai;
            this.email = email;
            this._quyen = quyen;
           
        }
       
        public bool ThemKH(string tenkh, string matkhau, string diachi, string dienthoai, string email,string quyen)
        {
            bool kq = true;

            try
            {
                string query = "insert into KhachHang(tenkh,matkhau,diachi,dienthoai,email,quyen) values('{0}','{1}','{2}','{3}','{4}','{5}')";
                query = String.Format(query, tenkh, matkhau, diachi, dienthoai, email,quyen);
                sqlConnection db = new sqlConnection();                
                SqlCommand cmd = new SqlCommand(query,db.GetConnect());
                return cmd.ExecuteNonQuery()>0;
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
         
         
        
        public bool XoaKH(string tenkh)
        {
            bool kq = true;

            try
            {
                string query = "Delete KhachHang where tenkh='{0}'";
                query = String.Format(query,tenkh);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query,db.GetConnect());
                cmd.ExecuteNonQuery();
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public bool CapNhatKH(string tenkh,string matkhau, string diachi, string dienthoai, string email,string quyen)
        {

            bool kq = true;

            try
            {
                string query = "update KhachHang set tenkh='{0}',matkhau='{1}',diachi='{2}',dienthoai='{3}',email='{4}',quyen='{5}' where tenkh='{6}'";
                query = String.Format(query,tenkh,matkhau, diachi, dienthoai, email,quyen,tenkh);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query,db.GetConnect());
               return cmd.ExecuteNonQuery()>0;
            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public static DataSet DSKH()
        {
            DataSet ds = new DataSet();
            string query = "select * from khachhang";
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds;
        }
        public static DataSet DSTenKH()
        {
            DataSet ds = new DataSet();
            string query = "select tenkh from khachhang";
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds;
        }
        
        public KhachHang ThongTinKH(string tenkh)
        {
            string query = "select * from KhachHang where tenkh='" + tenkh + "'";
            sqlConnection db=new sqlConnection();
            SqlCommand sc = new SqlCommand(query, db.GetConnect());            
            SqlDataReader reader = sc.ExecuteReader();
            KhachHang kh=new KhachHang();
            while (reader.Read())
            {                                
                kh.tenkh = reader.GetString(0);
                kh.matkhau = reader.GetString(1);
                kh.diachi = reader.GetString(2);
                kh.dienthoai = reader.GetString(3);
                kh.email = reader.GetString(4);
            }
            return kh;
        }
        public DataTable DangNhap(string tenkh, string matkhau)
        {
            DataSet ds = new DataSet();
            string query = "select * from khachhang where tenkh ='" + tenkh + "' and matkhau ='" + matkhau + "'";
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds.Tables[0];
        }
        public bool KiemTraTenTrung(string tendangnhap)
        {
            bool kq = true;

            try
            {
                string query = "select count(*) from khachhang where tenkh = N'" + tendangnhap.ToString() + "'";
                query = String.Format(query, tendangnhap);
                sqlConnection db = new sqlConnection();
                SqlCommand cmd = new SqlCommand(query, db.GetConnect());
                int n = int.Parse(cmd.ExecuteScalar().ToString());
                if (n > 0)
                {
                    kq = true;
                }
                else
                {
                    kq = false;
                }

            }
            catch
            {
                kq = false;
            }

            return kq;
        }
        public DataTable DangNhapAdmin(KhachHang kh)
        {
            DataSet ds = new DataSet();        
            sqlConnection db = new sqlConnection();
            SqlCommand command = new SqlCommand();
            command.Connection = db.GetConnect();
            command.CommandType = CommandType.Text;
            command.CommandText = @"select * from KhachHang where tenkh=@tenkh and matkhau=@matkhau";
            command.Parameters.Add("@tenkh", SqlDbType.NVarChar).Value = kh.tenkh;
            command.Parameters.Add("@matkhau", SqlDbType.NVarChar).Value = kh.matkhau;
            command.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            da.Fill(ds);        
            db.CloseConnect();
            
            return ds.Tables[0];
           
        }
        public static DataSet DSTenNhanVien()
        {
            DataSet ds = new DataSet();
            string query = "select * from khachhang where quyen=N'Nhân Viên'";
            sqlConnection db = new sqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
            sda.Fill(ds);
            return ds;
        }
    }
    }

